IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[csp_Institution_DeptInfoEd]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[csp_Institution_DeptInfoEd]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
--  csp_Institution_DeptInfoEd 2, 11, '503,28,650'
CREATE PROCEDURE [dbo].[csp_Institution_DeptInfoEd]      
(      
	@institutionId int ,
    @departmentId  int,
    @unitId varchar(255)    
)        
AS        
BEGIN    
		
		CREATE TABLE #temp 
				(Institution VARCHAR(255),
				 Department VARCHAR(255),
				 Unit VARCHAR(255))

		INSERT INTO #temp (Institution, Department, Unit)
		SELECT CASE @institutionId 
								WHEN 0 THEN 'ALL'		   
								ELSE Institution END,
					CASE @departmentId 
								WHEN 0 THEN 'ALL'		   
								ELSE [NAME] END,
							 CASE @unitId 
								WHEN '0' THEN 'ALL'
								ELSE NULL END
					FROM INSIGHT_RPT_DB.dbo.FwkDomainOrganization				
					WHERE FwkDomainOrganizationTypeId=3
					AND (InstitutionId =@institutionId OR @institutionId = 0) 
					AND (DepartmentId =@departmentId OR @departmentId = 0)	
	
	if EXISTS (SELECT unit FROM #temp WHERE Unit IS NULL)
	BEGIN
		DECLARE @localUnitNumber VARCHAR(255)
		DECLARE @unitName VARCHAR(255)		

		DECLARE unit_cursor CURSOR FOR 
		SELECT * FROM dbo.fnSplitFundNumbers(@unitId)	

		OPEN unit_cursor	
		FETCH NEXT FROM unit_cursor INTO @localUnitNumber

		WHILE @@FETCH_STATUS = 0
		BEGIN
			
			SELECT @unitName = [NAME] 
							FROM INSIGHT_RPT_DB.dbo.FwkDomainOrganization 
							WHERE  institutionid = @institutionId 
							AND DepartmentId = @departmentId
							AND UnitId = @localUnitNumber
							AND FwkDomainOrganizationTypeId = 4

			UPDATE #temp 
				SET unit = IsNull(unit,'') + ', ' + IsNull(@unitName,'')		

			FETCH NEXT 
			FROM unit_cursor
			INTO @localUnitNumber
				
		END

		CLOSE unit_cursor
		DEALLOCATE unit_cursor

		UPDATE #temp SET unit = Rtrim(Ltrim(substring(unit, 2, len(unit)+1)))	
	END

	SELECT DISTINCT Institution, Department, Unit FROM #temp

	DROP TABLE #temp
  
END   

GO

